<!DOCTYPE html>
<html>
    <head>
        <title>Pick-up Game</title>
        <link rel="stylesheet" href="style.css" type="text/css" />
        <script type="text/javascript" src="index.js"></script>
        <?php 
		 session_start();
         if (!empty($_SESSION['username'])) : 
         $conn_str = 'host=dbsrv1.cdf.toronto.edu dbname=csc309g23 user=csc309g23 password=ipheid5h';
		 $conn = pg_connect($conn_str);

        $query_num_users = 'SELECT COUNT(username) FROM members';
        $result_num_users = pg_query($conn, $query_num_users);
        
        $num_users = pg_fetch_row($result_num_users);
        //echo "Number of users currently registered: ".$num_users[0];
        
        $query_num_users_past_30 = 'SELECT COUNT(DISTINCT(username)) FROM games JOIN rosters ON (games.gameid=rosters.gameid) WHERE games.playstatus=TRUE AND games.start > (current_timestamp - interval \'3000 days\')';
        $result_active_users = pg_query($conn, $query_num_users_past_30);
        $num_active = pg_fetch_row($result_active_users);
        //echo "Number of active users : ".$num_active[0];
        
        $query_games_played = 'SELECT count(gameid) FROM games WHERe playstatus=TRUE';
        $result_games_played = pg_query($conn, $query_games_played);
        $games_played = pg_fetch_row($result_games_played);
        //echo "Number of games played : ".$games_played[0];
        
        $query_games_cancelled = 'SELECT count(gameid) FROM games WHERE playstatus=FALSE';
        $result_games_cancelled = pg_query($conn, $query_games_cancelled);
        $games_cancelled = pg_fetch_row($result_games_cancelled);
        //echo "Number of games cancelled : ".$games_cancelled[0];
        
        $query_avg_rating = 'SELECT AVG(orgrating),AVG(playrating) FROM members';
        $result_avg_rating = pg_query($conn, $query_avg_rating);
        $avg_rating = pg_fetch_row($result_avg_rating);
        //echo "Avg Prating: ".$avg_rating[1]." Avg Orating: ".$avg_rating[0];
        
        $q_sport_game_freq = 'SELECT sport,COUNT(gameid) AS frequnecy FROM games GROUP BY sport';
        $r_sport_game_freq = pg_query($conn, $q_sport_game_freq);
        
        ?>
        <!--Load the AJAX API-->
		<script type="text/javascript" src="https://www.google.com/jsapi"></script>
		<script type="text/javascript">

		// Load the Visualization API and the piechart package.
		google.load('visualization', '1.0', {'packages':['corechart']});

		// Set a callback to run when the Google Visualization API is loaded.
		google.setOnLoadCallback(drawSportChart);
		google.setOnLoadCallback(drawUsersChart);
		google.setOnLoadCallback(drawGamesChart);
  
		// Callback that creates and populates a data table,
		// instantiates the pie chart, passes in the data and
		// draws it.
		function drawSportChart() {

		  // Create the sport data table.
		  var sport_data = new google.visualization.DataTable();
		  sport_data.addColumn('string', 'Sport played');
		  sport_data.addColumn('number', 'Frequency of games');
		  sport_data.addRows([
		  <?php 	
				for($i=0; $i<pg_num_rows($r_sport_game_freq)-2;++$i){
					$array_sports = pg_fetch_assoc($r_sport_game_freq);
					echo "['".$array_sports['sport']."',".$array_sports['frequnecy']."],\n";
				}
					$array_sports = pg_fetch_assoc($r_sport_game_freq);
					echo "['".$array_sports['sport']."',".$array_sports['frequnecy']."]\n";
				 ?>
		  ]);// Create the data table.
		  
		  
		  

        // Set chart options
        var sport_options = {'title':'Sports chart',
                       'width':500,
                       'height':400};
                       
                      

        // Instantiate and draw our chart, passing in some options.
        var sport_chart = new google.visualization.PieChart(document.getElementById('sport_chart_div'));
        sport_chart.draw(sport_data, sport_options);
        
      }
      
      
      
      function drawUsersChart() {
		  // Create the users data table.
			var user_data = new google.visualization.DataTable();
			user_data.addColumn('string', 'Users');
			user_data.addColumn('number', 'Frequency');
			user_data.addRows([
								['Currently registered users', <?=$num_users[0]?>],
								['Active users', <?=$num_active[0]?>]
			]);
			
			// Set chart options
        var user_options = {'title':'Users chart',
                       'width':600,
                       'height':300}; 
        var user_chart = new google.visualization.BarChart(document.getElementById('user_chart_div'));
        user_chart.draw(user_data, user_options);
	}
	
	function drawGamesChart() {
		  // Create the users data table.
			var game_data = new google.visualization.DataTable();
			game_data.addColumn('string', 'Games');
			game_data.addColumn('number', 'Frequency');
			game_data.addRows([
								['Games played', <?=$games_played[0]?>],
								['Games canceled', <?=$games_cancelled[0]?>]
			]);
			
			// Set chart options
        var game_options = {'title':'Games Chart',
                       'width':600,
                       'height':300}; 
        var game_chart = new google.visualization.ColumnChart(document.getElementById('game_chart_div'));
        game_chart.draw(game_data, game_options);
	}
    </script>

        
       
    </head>

    <body>

        <?php include_once('menu.php') ?>

        <div class="container">
        <center><div id="user_chart_div" style="border-bottom:1px solid silver;"></div></center>        
        <center><div id="game_chart_div" style="border-bottom:1px solid silver;"></div></center>
		<center><div id="sport_chart_div" ></div></center>
		
		
        <?php
        else :
            header('Location: /');
        endif;
        ?>
        
        </div>
    </body>

</html>
